package com.supermap.dataservice.cloud.market;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @description
 * @date 2018年11月22 下午 9:29
 * @email 1301584033@qq.com
 */
@Mapper
public interface MarketinfoMapper {

    /**
     * 获取最近一天
     */
    @Select("select t1.code,t1.name,t1.marketname,t1.avgprice,t1.maxprice,t1.minprice,t1.unit,t1.creatdate  from t_marketinfo t1 \n" +
            "left join t_marketinfo t2 \n" +
            "on t1.marketcode = t2.marketcode and t1.code = t2.code and t1.creatdate <= t2.creatdate\n" +
            "where t1.marketname=#{marketname}  and t1.marketcode   like concat(#{areaCode},'%') and t1.code like concat(#{cropstype},'%')\n" +
            "group by t1.marketcode,t1.code, t1.creatdate\n" +
            "having count(t2.creatdate) <=1")
    List<Map<String,Object>>  getLastOntDay(@Param("areaCode")String areaCode,@Param("cropstype")String cropstype,@Param("marketname")String marketname);

    /***
     * 获取所有区域
     * */
    @Select("select areacode,areaname from t_area where length(areacode)=2")
    List<Map<String,Object>> getAllArea();

    /***
     * 获取所有区域
     * */
    @Select("select distinct marketname from t_marketinfo where marketcode like concat(#{areaCode},'%') ")
    List<Map<String,Object>> getAllMarktNameByAreaCode(@Param("areaCode") String areaCode);


    /**
     * 获取最近7次
     */
    @Select("select marketname,unit, group_concat(avgprice order by creatdate asc) avgprice, group_concat(maxprice order by creatdate asc) maxprice, group_concat(minprice order by creatdate asc) minprice, group_concat(creatdate order by creatdate asc) creatdate\n" +
            " from \n" +
            "(select t1.* from t_marketinfo t1  \n" +
            "left join t_marketinfo t2 \n" +
            "on t1.marketcode = t2.marketcode and t1.code = t2.code and t1.creatdate <= t2.creatdate\n" +
            "where t1.marketname=#{marketname}  and  t1.marketcode   like concat(#{areaCode},'%') and t1.code =  #{code} \n" +
            "group by t1.marketcode,t1.code, t1.creatdate\n" +
            "having count(t2.creatdate) <=7) t\n" +
            "group by t.marketcode\n" +
            "; ")
    List<Map<String,Object>>  getLastSevenDay(@Param("areaCode")String areaCode,@Param("code")String code,@Param("marketname")String marketname);


    /***
     *获取所有农贸市场
     * */
    @Select("select t1.market from t_marketinfo t1  group by market")
    List<Map<String,Object>> getMarketTitle();
    /***
     *根据农贸市场获取最近一次商品信息
     * */
    @Select("select t1.market,t1.markdate,t1.goods,t1.price,t1.unit from t_marketinfo t1 \n" +
            "left join t_marketinfo t2 \n" +
            "on t1.market = t2.market and t1.goods = t2.goods and t1.markdate <= t2.markdate\n" +
            "where t1.market= #{marketTitle} \n" +
            "group by t1.market,t1.goods, t1.markdate\n" +
            "having count(t2.markdate) <=1")
    List<Map<String,Object>>  getlatelyFirstMarketinfo (@Param("marketTitle") String marketTitle);
    /***
     *根据农贸市场和商品名称获取最近七次商品信息
     * */
    @Select("select t1.market,t1.markdate,t1.goods,t1.price,t1.unit from (select t1.market,t1.markdate,t1.goods,t1.price,t1.unit from t_marketinfo t1 where t1.market=#{marketTitle} and t1.goods=#{goods} order by markdate desc limit 7) t1\n" +
            "order by markdate asc")
    List<Map<String,Object>>  getlatelyTop7Market (@Param("marketTitle") String marketTitle,@Param("goods") String goods);
 }
